package com.taru.order.dao;


import com.taru.order.entity.Articl;
import com.taru.order.entity.Category;
import com.taru.order.util.DbUtil;
import com.taru.order.util.StringUtil;

import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class CateDaoImpl  {

    ArtDaoImpl artDao=new ArtDaoImpl();

    /**
     * 获取分类
     * @return
     */
    public List<Category> getCategories() {
        String sql="select * from `tb_cate`";
        List<Map<String,Object>> list = DbUtil.query(sql);
        List<Category> categoryList=new ArrayList<>();
        if(list!=null && list.size()>0){
            for(Map<String,Object> map:list){
                Category category=new Category();
                category.setCateId(StringUtil.valueOf(map.get("cate_id")));
                category.setCateName(StringUtil.valueOf(map.get("cate_name")));
                long number=getCateTotal(category.getCateId());
                category.setNumber(number);
                categoryList.add(category);
            }
        }
        DbUtil.close();
        return categoryList;
    }
    /**
     * 获取各个分类总数
     */
    public Long getCateTotal(String cate_id){
        String sql="select count(*) as count from `tb_articl`  where  cate_id=?";
        List<Map<String, Object>> list = DbUtil.query(sql,cate_id);
        Long number=null;
        if (list != null && list.size() > 0) {
            for (Map<String, Object> map : list) {
                number=(Long) map.get("count");
            }
        }
        DbUtil.close();
        return number;
    }
    /**
     * 添加分类
     * @param category
     * @return
     */
    public int addCategory(Category category) {
        String sql="insert into `tb_cate`(cate_name) values(?)";
        int code=DbUtil.update(sql,category.getCateName());
        return code;
    }

    /**
     * 修改分类
     * @param category
     */
    public int modCategory(Category category)  {
        String sql="update `tb_cate` set cate_name=? where cate_id=? ";
        int code=DbUtil.update(sql,category.getCateName(),category.getCateId());
        return code;
    }
    /**
     * 批量删除分类
     * @param list
     * @throws IOException
     */
    public int removeCategories(List<Integer> list) throws Exception {
        StringBuffer stringBuffer=new StringBuffer();
        for(int i=0;i<list.size();i++){
            stringBuffer.append("\'");
            stringBuffer.append(list.get(i));
            stringBuffer.append("\',");
        }
        String sql="delete from `tb_cate` where cate_id in ("+stringBuffer.substring(0,stringBuffer.length()-1)+")";
        int code=0;
        DbUtil.beginTransaction();
        try {
            code=DbUtil.update(sql);
            DbUtil.commit();
        }catch (Exception e){
            e.printStackTrace();
            DbUtil.rollback();
            throw new Exception("DML异常");
        }
        DbUtil.close();
        return code;
    }
    /**
     * 根据cate_id获取想要的类型文章(已发布)
     */
    public List listImgArticl(Integer pageNum,Integer pageSize,String cate_id){
        String sql="select t.* from `tb_articl` t where ispublish='1' and cate_id=? order by create_time desc limit ?,?";
        List<Map<String, Object>> list = DbUtil.query(sql,cate_id,pageNum,pageSize);
        List<Articl> articlList = new ArrayList<>();
        if (list != null && list.size() > 0) {
            for (Map<String, Object> map : list) {
                Articl articl = new Articl();
                articl.setId(StringUtil.valueOf(map.get("articl_id")));
                articl.setCode(StringUtil.valueOf(map.get("articl_code")));
                articl.setContext(StringUtil.valueOf(map.get("context")));
                articl.setLogo(StringUtil.valueOf(map.get("logo")));
                articl.setCreateTime(StringUtil.valueOf(map.get("create_time")));
                articl.setModTime(StringUtil.valueOf(map.get("mod_time")));
                articl.setCateId(StringUtil.valueOf(map.get("cate_id")));
                articl.setIsEnd(StringUtil.valueOf(map.get("isend")));
                articl.setLikes((Integer) map.get("likes"));
                articl.setTitle(StringUtil.valueOf(map.get("title")));
                articl.setUserId(StringUtil.valueOf(map.get("user_id")));
                articl.setIsPublish(StringUtil.valueOf(map.get("ispublish")));
                articlList.add(articl);
            }
        }
        DbUtil.close();
        return articlList;
    }
    /**
     * 根据cate_id查询一篇文章
     */
    public Articl listArticlOne(String cate_id){
        String sql="select t.* from `tb_articl` t where ispublish='1' and cate_id=? order by create_time desc";
        List<Map<String, Object>> list = DbUtil.query(sql,cate_id);
        String articlId=null;
        if (list != null && list.size() > 0) {
            Map<String, Object> map =list.get(0);
            articlId=StringUtil.valueOf(map.get("articl_id"));
        }
        Articl articl=artDao.getArticlById(articlId);
        DbUtil.close();
        return articl;
    }
    /**
     * 分页获取所有最新文章
     */
    public List listAllArticls(String cateId, Integer pageNum, Integer pageSize)  {
        String sql = "select t.* from (SELECT ( @rowNO := @rowNo + 1 ) AS rowno,a.* FROM ( SELECT * FROM `tb_articl` where cate_id=? ORDER BY create_time desc ) a,( SELECT @rowNO := 0 ) b) t where rowno<=? and rowno>=?";
        List<Map<String, Object>> list = DbUtil.query(sql,cateId,pageNum*pageSize, (pageNum-1)*pageSize+1);
        List<Articl> articlList = new ArrayList<>();
        if (list != null && list.size() > 0) {
            for (Map<String, Object> map : list) {
                Articl articl = new Articl();
                articl.setId(StringUtil.valueOf(map.get("articl_id")));
                articl.setCode(StringUtil.valueOf(map.get("articl_code")));
                articl.setContext(StringUtil.valueOf(map.get("context")));
                articl.setLogo(StringUtil.valueOf(map.get("logo")));
                articl.setCreateTime(StringUtil.valueOf(map.get("create_time")));
                articl.setModTime(StringUtil.valueOf(map.get("mod_time")));
                articl.setCateId(StringUtil.valueOf(map.get("cate_id")));
                articl.setIsEnd(StringUtil.valueOf(map.get("isend")));
                articl.setLikes((Integer) map.get("likes"));
                articl.setTitle(StringUtil.valueOf(map.get("title")));
                articl.setUserId(StringUtil.valueOf(map.get("user_id")));
                articl.setIsPublish(StringUtil.valueOf(map.get("ispublish")));
                articlList.add(articl);
            }
        }
        DbUtil.close();
        return articlList;
    }
    /**
     * 根据cate_id获取所有的列表数
     */
    public Long queryToral(String cateId) {
        String sql=" SELECT count(*) as count FROM `tb_articl` where cate_id=?";
        List<Map<String,Object>> list=DbUtil.query(sql,cateId);
        Long toral=null;
        if(list!=null && list.size()>0){
            Map<String,Object> map=list.get(0);
            toral= (Long)map.get("count");
        }
        DbUtil.close();
        return toral;
    }
    /**
     * 月度热榜
     */
    public  List<Articl> listArticlsByMonth(String cateId,String year,String month){
        String sql="select t.* from `tb_articl` t where ispublish='1' and cate_id=? and YEAR(create_time) = ? and MONTH(create_time)=? order by likes desc";
        List<Map<String, Object>> list = DbUtil.query(sql,cateId,year,month);
        List<Articl> articlList = new ArrayList<>();
        if (list != null && list.size() > 0) {
            for (Map<String, Object> map : list) {
                Articl articl = new Articl();
                articl.setId(StringUtil.valueOf(map.get("articl_id")));
                articl.setCode(StringUtil.valueOf(map.get("articl_code")));
                articl.setContext(StringUtil.valueOf(map.get("context")));
                articl.setLogo(StringUtil.valueOf(map.get("logo")));
                articl.setCreateTime(StringUtil.valueOf(map.get("create_time")));
                articl.setModTime(StringUtil.valueOf(map.get("mod_time")));
                articl.setCateId(StringUtil.valueOf(map.get("cate_id")));
                articl.setIsEnd(StringUtil.valueOf(map.get("isend")));
                articl.setLikes((Integer) map.get("likes"));
                articl.setTitle(StringUtil.valueOf(map.get("title")));
                articl.setUserId(StringUtil.valueOf(map.get("user_id")));
                articl.setIsPublish(StringUtil.valueOf(map.get("ispublish")));
                articlList.add(articl);
            }
        }
        DbUtil.close();
        return articlList;
    }

    /**
     * 年度热榜
     */
    public  List<Articl> listArticlsByYear(String cateId,String year){
        String sql="select t.* from `tb_articl` t where ispublish='1' and cate_id=? and YEAR(create_time) = ?  order by likes desc";
        List<Map<String, Object>> list = DbUtil.query(sql,cateId,year);
        List<Articl> articlList = new ArrayList<>();
        if (list != null && list.size() > 0) {
            for (Map<String, Object> map : list) {
                Articl articl = new Articl();
                articl.setId(StringUtil.valueOf(map.get("articl_id")));
                articl.setCode(StringUtil.valueOf(map.get("articl_code")));
                articl.setContext(StringUtil.valueOf(map.get("context")));
                articl.setLogo(StringUtil.valueOf(map.get("logo")));
                articl.setCreateTime(StringUtil.valueOf(map.get("create_time")));
                articl.setModTime(StringUtil.valueOf(map.get("mod_time")));
                articl.setCateId(StringUtil.valueOf(map.get("cate_id")));
                articl.setIsEnd(StringUtil.valueOf(map.get("isend")));
                articl.setLikes((Integer) map.get("likes"));
                articl.setTitle(StringUtil.valueOf(map.get("title")));
                articl.setUserId(StringUtil.valueOf(map.get("user_id")));
                articl.setIsPublish(StringUtil.valueOf(map.get("ispublish")));
                articlList.add(articl);
            }
        }
        DbUtil.close();
        return articlList;
    }
    /**
     * 总共多少条
     */
    public Long getTotal(String cateId){
        String sql="select count(*) from tb_articl where cate_id=?";
        List<Map<String,Object>> list=DbUtil.query(sql,cateId);
        Long total=null;
        if(list!=null && list.size()>0){
            Map<String,Object> map=list.get(0);
            total=(Long) map.get("count(*)");
        }
        return total;
    }
}
